use crate::commands::user_settings::get_user_settings;
use oracle::Connection;
use serde_json;
use log::{debug, error, info};

/// 执行SQL查询并返回结果
#[tauri::command]
pub async fn execute_sql_query(query: String) -> Result<Vec<serde_json::Value>, String> {
    info!("开始执行SQL查询: {}", query);

    let settings = get_user_settings();
    let config = settings.database;

    // 构建Oracle连接字符串
    let connect_string = format!("{}:{}/{}", config.host, config.port, config.service_name);
    info!("数据库连接字符串: {}", connect_string);

    match tokio::task::spawn_blocking(move || {
        match Connection::connect(&config.username, &config.password, &connect_string) {
            Ok(conn) => {
                info!("数据库连接成功");
                match conn.query(&query, &[]) {
                    Ok(rows) => {
                        let mut results = Vec::new();
                        let mut row_count = 0;

                        for row_result in rows {
                            match row_result {
                                Ok(row) => {
                                    let mut row_data = serde_json::Map::new();

                                    // 获取列信息
                                    let info = row.column_info();
                                    for (i, col_info) in info.iter().enumerate() {
                                        let col_name = col_info.name();

                                        // 根据列类型获取值
                                        let value: serde_json::Value = match row
                                            .get::<usize, Option<String>>(i)
                                        {
                                            Ok(Some(s)) => serde_json::Value::String(s),
                                            Ok(None) => serde_json::Value::Null,
                                            Err(_) => {
                                                // 尝试其他类型
                                                match row.get::<usize, Option<i64>>(i) {
                                                    Ok(Some(n)) => serde_json::Value::Number(
                                                        serde_json::Number::from(n),
                                                    ),
                                                    Ok(None) => serde_json::Value::Null,
                                                    Err(_) => {
                                                        // 尝试浮点数
                                                        match row.get::<usize, Option<f64>>(i) {
                                                            Ok(Some(f)) => {
                                                                if let Some(num) =
                                                                    serde_json::Number::from_f64(f)
                                                                {
                                                                    serde_json::Value::Number(num)
                                                                } else {
                                                                    serde_json::Value::Null
                                                                }
                                                            }
                                                            Ok(None) => serde_json::Value::Null,
                                                            Err(_) => serde_json::Value::Null,
                                                        }
                                                    }
                                                }
                                            }
                                        };

                                        row_data.insert(col_name.to_string(), value);
                                    }

                                    results.push(serde_json::Value::Object(row_data));
                                    row_count += 1;
                                }
                                Err(e) => {
                                    let error_msg = format!("读取行数据失败: {}", e);
                                    error!("{}", error_msg);
                                    return Err(error_msg);
                                }
                            }
                        }

                        info!("查询执行成功，返回 {} 行数据", row_count);
                        Ok(results)
                    }
                    Err(e) => {
                        let error_msg = format!("查询执行失败: {}", e);
                        error!("{}", error_msg);
                        Err(error_msg)
                    }
                }
            }
            Err(e) => {
                let error_msg = format!("数据库连接失败: {}", e);
                error!("{}", error_msg);
                Err(error_msg)
            }
        }
    })
        .await
    {
        Ok(result) => result,
        Err(e) => {
            let error_msg = format!("查询任务执行失败: {}", e);
            error!("{}", error_msg);
            Err(error_msg)
        }
    }
}

/// 获取工作流列表
#[tauri::command]
pub async fn get_workflow_list() -> Result<Vec<serde_json::Value>, String> {
    info!("开始获取工作流列表");
    let query = "SELECT wfid, wfname FROM tb_upe_model ORDER BY wfname".to_string();

    match execute_sql_query(query).await {
        Ok(results) => {
            info!("成功获取工作流列表，共 {} 个工作流", results.len());
            Ok(results)
        }
        Err(e) => {
            error!("获取工作流列表失败: {}", e);
            Err(e)
        }
    }
}

/// 获取指定工作流的XML内容
#[tauri::command]
pub async fn get_workflow_xml(wfid: String) -> Result<serde_json::Value, String> {
    info!("开始获取工作流XML内容，wfid: {}", wfid);

    let query = format!(
        "SELECT wfcontent FROM (SELECT wfcontent FROM tb_upe_modelver WHERE wfid = '{}' ORDER BY versionno DESC) WHERE ROWNUM = 1",
        wfid
    );

    debug!("执行查询: {}", query);

    match execute_sql_query(query).await {
        Ok(mut results) => {
            if let Some(row) = results.pop() {
                info!("成功获取工作流XML内容，wfid: {}", wfid);
                Ok(row)
            } else {
                let error_msg = format!("未找到工作流ID为 '{}' 的XML内容", wfid);
                error!("{}", error_msg);
                Err(error_msg)
            }
        }
        Err(e) => {
            error!("获取工作流XML内容失败，wfid: {}, 错误: {}", wfid, e);
            Err(e)
        }
    }
}

/// 测试数据库连接（使用当前配置）
#[tauri::command]
pub async fn test_db_connection() -> Result<bool, String> {
    info!("开始测试数据库连接");

    let settings = get_user_settings();
    let config = settings.database;

    // 复用现有的测试连接逻辑
    match crate::commands::user_settings::test_database_connection(config).await {
        Ok(_) => {
            info!("数据库连接测试成功");
            Ok(true)
        }
        Err(e) => {
            error!("数据库连接测试失败: {}", e);
            Ok(false)
        }
    }
}